﻿using System;
using System.Collections.Generic;
using System.Data;
 
using System.Reflection;
using System.Text;
 

namespace OS.Common
{
    public class CommonMethod
    {
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataTable GetList(string sql)
        {
            return SQLHelper.Query(sql).Tables[0];
        }

        public bool IsExistDqck(string tmh)
        {
            string sql = "select  dqkc  from spdaH where tmh='" + tmh + "'";
             
            object obj = SQLHelper.GetSingle(sql);
            if (obj != null && obj.ToString() != "" && decimal.Parse(obj.ToString()) > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 获取图片显示状态
        /// </summary>
        /// <returns>状态 0  普通、1 最新、2 热卖、3 推荐</returns>
        public int GetImgShowState(string djsj, int Dgz, decimal bs)
        {
            int status = 0;
            int minDgz = 0;

            DateTime newTime = DateTime.Now;
            DateTime oldTime = DateTime.Now.AddDays(-3);
            DateTime time = Convert.ToDateTime(djsj);

            string sql = "select top 200 dgz from spdah order by dgz desc";

            DataTable dt = SQLHelper.Query(sql).Tables[0];


            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt.Rows[dt.Rows.Count - 1][0].ToString() != "")
                {
                    minDgz = int.Parse(dt.Rows[dt.Rows.Count - 1][0].ToString());
                }
            }


            //判断最新
            if (time >= oldTime && time <= newTime)
            {
                status = 1;
            }
            else
            {
                //判断热销
                if (bs < 1 & bs > 0)
                {
                    status = 3;
                }
                //判断推荐
                else if (Dgz >= minDgz)
                {
                    status = 2;
                }
            }
            return status;
        }
    
        #region 购物车操作
        /// <summary>
        /// 修改购物车 
        /// 创建时间 2012-12-13 刘章明
        /// 修改时间 2015-05-12
        /// </summary>
        /// <param name="product_sn">商品编号</param>
        /// <param name="product_quantity">数量</param>
        /// <param name="user_sn">用户编号</param>
        /// <param name="cart_add_time">添加时间</param>
        /// <returns></returns>
        public int UpdateCart(string product_sn, string product_quantity, string user_sn, string cart_add_time, string content)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update web_cart set product_quantity=" + product_quantity + ",");
            sb.Append("cart_add_time='" + cart_add_time + "',sm='" + content + "'  where product_sn='" + product_sn + "'and user_sn='" + user_sn + "'");
            int ss = SQLHelper.ExecuteSql(sb.ToString());
            return ss;
        }

        public int UpdateYuding(string product_sn, string product_quantity, string djlsh)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update ygddB set disl=" + product_quantity + ",dhxj=pfj*qdpl*" + product_quantity + ",slxj=qdpl*" + product_quantity + " where spbh='" + product_sn + "' and djlsh=" + djlsh);
             int ss = SQLHelper.ExecuteSql(sb.ToString());
            return ss;
        }

        public List<webCart_Entity> GetCartList(string userId, string filter)
        {
            string sql = " select a.product_sn, sm, a.product_quantity sl, product_name,case when a.status is null then 1 else a.status end status,convert(numeric(19,2),product_market_price) product_market_price,convert(numeric(19,2),product_wholesale_price) product_wholesale_price,b.product_qdpl,product_units,b.product_pfzk from web_cart a left join web_product_list_show_st b on a.product_sn=b.product_sn where a.user_sn='" + userId + "'" + filter;
            List<webCart_Entity> ls = null;
            DataTable dt = SQLHelper.ExecuteDt(sql);

            dt = CreateCartData(dt);

            if (dt != null && dt.Rows.Count > 0)
            {
                ls = ConvertToEntity(dt);
            }
            return ls;
        }
        public List<webCart_Entity> GetCartList(string userId)
        {
            string sql = " select a.product_sn, sm, a.product_quantity sl, product_name,case when a.status is null then 1 else a.status end status,convert(numeric(19,2),product_market_price) product_market_price,convert(numeric(19,2),product_wholesale_price) product_wholesale_price,b.product_qdpl,product_units,b.product_pfzk from web_cart a left join web_product_list_show_st b on a.product_sn=b.product_sn where a.user_sn='" + userId + "'";
            List<webCart_Entity> ls = null;
            DataTable dt = SQLHelper.ExecuteDt(sql);

            //   dt = CreateCartData(dt);

            if (dt != null && dt.Rows.Count > 0)
            {
                ls = ConvertToEntity(dt);
            }
            return ls;
        }

        public DataTable CreateCartData(DataTable dt)
        {
            dt.Columns.Add("xj");
            dt.Columns.Add("status2");
            dt.Columns.Add("index");
            decimal marketPrice = 0;

            decimal Price = 0;

            decimal productQdpl = 0;
            decimal quantity = 0;
            decimal pfzk = 0;
            decimal xj = 0;

            decimal all = 0;
            int num = 0;
            int index = 1;
            foreach (DataRow dr in dt.Rows)
            {

                if (dr["product_market_price"] != null && dr["product_market_price"].ToString() != "")
                {
                    Price = decimal.Parse(dr["product_market_price"].ToString());
                }
                else
                {
                    Price = 0;
                    dr["product_market_price"] = Price;
                }


                if (dr["product_wholesale_price"] != null && dr["product_wholesale_price"].ToString() != "")
                {
                    marketPrice = decimal.Parse(dr["product_wholesale_price"].ToString());
                }
                else
                {
                    marketPrice = 0;
                    dr["product_wholesale_price"] = marketPrice;
                }

                if (dr["sl"] != null && dr["sl"].ToString() != "")
                {
                    quantity = decimal.Parse(dr["sl"].ToString());
                }
                else
                {
                    quantity = 0;
                    dr["sl"] = quantity;
                }

                if (dr["product_qdpl"] != null && dr["product_qdpl"].ToString() != "")
                {
                    productQdpl = decimal.Parse(dr["product_qdpl"].ToString());
                }
                else
                {
                    productQdpl = 0;
                    dr["product_qdpl"] = productQdpl;
                }

                if (dr["product_pfzk"] != null && dr["product_pfzk"].ToString() != "")
                {
                    pfzk = decimal.Parse(dr["product_pfzk"].ToString());
                }
                else
                {
                    pfzk = 0;
                    dr["product_pfzk"] = pfzk;
                }


                xj = marketPrice * productQdpl * quantity * pfzk;

                all += xj;
                num += Convert.ToInt32(dr["sl"]);

                dr.BeginEdit();

                dr["xj"] = xj;
                dr["index"] = index.ToString();
                dr.EndEdit();
                index++;
            }

            DataRow ndr = dt.NewRow();
            ndr["index"] = "总计：";
            ndr["sl"] = num;
            ndr["status"] = -1;
            ndr["xj"] = all;
            dt.Rows.Add(ndr);
            return dt;
        }


        public DataTable GetWebProductInfo(string product_sn)
        {
            string sql = "select top 1 product_pfzk pfzk,product_sn,product_name,product_units,product_intro,type_sn,type_name,product_market_price,product_wholesale_price,product_qdpl,product_add_time,product_zt,product_fhljs,spgg,product_cwm,product_tmh from web_product_list_show_st  where product_sn='" + product_sn + "'";
            DataTable dt = SQLHelper.ExecuteDt(sql);
            return dt;
        }



        public string Get_Dmck(string id, string dmbh)
        {
            string dmck = "0";
            try
            {

                string sql = "select top 1 yy,mm,spbh,sum(qmsl) as qmsl  from dmkczbh where spbh='" + id + "' and dmbh='" + dmbh + "' group by  yy,mm,spbh order by yy desc,mm desc ";
                DataTable dt = SQLHelper.ExecuteDt(sql);

                if (dt != null && dt.Rows.Count > 0)
                {
                    dmck = dt.Rows[0]["qmsl"].ToString();
                }
            }
            catch { }


            return dmck;
        }

        public string Get_Dmxsljsl(string id, string dmbh)
        {

            string sql = "select top 1 sum(sl) from xskdB left join xskdh on  xskdB.djlsh=xskdh.djlsh where xskdB.spbh='" + id + "'and xskdh.bmbh='" + dmbh + "'";
            DataTable dt = SQLHelper.ExecuteDt(sql);

            string dmxsljsl = "0";

            if (dt != null && dt.Rows.Count > 0)
            {
                dmxsljsl = dt.Rows[0][0].ToString();
                if (dmxsljsl == "")
                    dmxsljsl = "0";
            }
            return dmxsljsl;

        }

        public string Get_Dmjhsj(string id, string dmbh)
        {
            string sql = "select top 1 zhjhsj from dmspdah where spbh='" + id + "' and dmbh='" + dmbh + "'";
            DataTable dt = SQLHelper.ExecuteDt(sql);

            string zhjhsj = "";

            if (dt != null && dt.Rows.Count > 0)
            {
                zhjhsj = dt.Rows[0][0].ToString();
            }
            return zhjhsj;

        }


        /// <summary>
        /// 修改购物车(商品列表页面 订购)
        /// </summary>
        /// <param name="product_sn"></param>
        /// <param name="product_quantity"></param>
        /// <param name="user_sn"></param>
        /// <param name="cart_add_time"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpdateCart(string product_sn, string product_quantity, string user_sn, string cart_add_time, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update web_cart set product_quantity=product_quantity+" + product_quantity + ",");
            sb.Append("cart_add_time='" + cart_add_time + "',status=" + status + "  where product_sn='" + product_sn + "'and user_sn='" + user_sn + "'");
            int ss = SQLHelper.ExecuteSql(sb.ToString());
            return ss;
        }
        /// <summary>
        /// 修改购物车4
        /// </summary>
        /// <param name="product_sn">商品编号</param>
        /// <param name="product_quantity">数量</param>
        /// <param name="user_sn">用户编号</param>
        /// <param name="cart_add_time">添加时间</param>
        /// <returns></returns>
        public int UpdateCart(string product_sn, string product_quantity, string user_sn, string cart_add_time, int status, string content)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update web_cart set product_quantity=product_quantity+" + product_quantity + ",");
            sb.Append("cart_add_time='" + cart_add_time + "',status=" + status + ",sm='" + content + "'  where product_sn='" + product_sn + "'and user_sn='" + user_sn + "'");
            int ss = SQLHelper.ExecuteSql(sb.ToString());
            return ss;
        }

        /// <summary>
        /// 添加购物车(商品列表页面 订购)
        /// </summary>
        /// <param name="product_sn"></param>
        /// <param name="product_quantity"></param>
        /// <param name="user_sn"></param>
        /// <param name="cart_add_time"></param>
        /// <param name="hh"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int AddCart(string product_sn, string product_quantity, string user_sn, string cart_add_time, int hh, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into web_cart( product_sn,product_quantity,user_sn,cart_add_time,hh,status) values(");

            sb.Append("'" + product_sn + "'," + product_quantity + ",'" + user_sn + "','" + cart_add_time + "'," + hh + "," + status + ")");

            int ss = SQLHelper.ExecuteSql(sb.ToString());
            return ss;
        }
        public int AddCart(string product_sn, string product_quantity, string user_sn, string cart_add_time, int hh, int status, string content)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into web_cart( product_sn,product_quantity,user_sn,cart_add_time,hh,status,sm) values(");

            sb.Append("'" + product_sn + "'," + product_quantity + ",'" + user_sn + "','" + cart_add_time + "'," + hh + "," + status + ",'" + content + "')");

            int ss = SQLHelper.ExecuteSql(sb.ToString());

            return ss;
        }


        /// <summary>
        /// 批量删除购物车
        /// </summary>
        /// <param name="values"></param>
        /// <param name="user_sn"></param>
        public int DeleteCartList(string values, string user_sn)
        {
            int success = 0;
            string[] value = values.Split(',');

            List<string> ls = new List<string>();

            for (int i = 0; i < value.Length; i++)
            {
                if (value[i] != "")
                {
                    string sql = GetDeleteStr(value[i], user_sn);
                    ls.Add(sql);
                }
            }
            if (ls.Count > 0)
            {
                success = SQLHelper.ExecuteSqlTran(ls);
            }
            return success;
        }
        public int ClearCart(string user_sn)
        {
            string sql = "delete web_cart  where user_sn='" + user_sn + "'";
            int ss = SQLHelper.ExecuteSql(sql);
            return ss;
        }

        public string GetDeleteStr(string product_sn, string user_sn)
        {
            string sql = "delete web_cart  where product_sn='" + product_sn + "' and user_sn='" + user_sn + "'";
            return sql;
        }

     

        private string Update(string product_sn, string user_sn, string cart_add_time, string product_quantity, string sm)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update web_cart set product_quantity=" + product_quantity + ",");
            sb.Append(" cart_add_time='" + cart_add_time + "',sm= '" + sm + "'  where product_sn='" + product_sn + "'and user_sn='" + user_sn + "'");
            return sb.ToString();
        }


        public string Update1(string product_sn, string user_sn, string cart_add_time, string product_quantity)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update web_cart set product_quantity=" + product_quantity + ",");
            sb.Append(" cart_add_time='" + cart_add_time + "'   where product_sn='" + product_sn + "'and user_sn='" + user_sn + "'");
            return sb.ToString();
        }
        public string Update2(string product_sn, string user_sn, string cart_add_time, string sm)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update web_cart set ");
            sb.Append(" cart_add_time='" + cart_add_time + "',sm= '" + sm + "'  where product_sn='" + product_sn + "'and user_sn='" + user_sn + "'");
            return sb.ToString();
        }

        public int UpdateList(List<string> ls)
        {
            int s = SQLHelper.ExecuteSqlTran(ls);
            return s;
        }

        public int DeleteCartByProduct(string user_sn, string product_sn)
        {
            string sql = "delete web_cart  where user_sn='" + user_sn + "'  and  product_sn='" + product_sn + "'";
            int ss = SQLHelper.ExecuteSql(sql);
            return ss;
        }


        /// <summary>
        /// 获取购物车数据
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetCartData(string userId)
        {
            string sql = "select a.product_sn, sm, a.product_quantity sl, product_name,case when a.status is null then 1 else a.status end status,convert(numeric(19,2),product_market_price) product_market_price,convert(numeric(19,2),product_wholesale_price) product_wholesale_price,b.product_qdpl,product_units,b.product_pfzk from web_cart a left join web_product_list_show_st b on a.product_sn=b.product_sn    where a.user_sn='" + userId + "'";
            DataTable dt = SQLHelper.ExecuteDt(sql);
            return dt;
        }
        #region 生成订单 预订单




        /// <summary>
        /// 生成订单  表头
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userdmmc"></param>
        /// <returns></returns>
        public string GetWsdhdHStr(string userId, string userdmmc, int djlsh, int dgsl, decimal dgje)
        {
            StringBuilder sb = new StringBuilder();
            string dhdh = GetNewCgdbh();

            int hh1 = GetHh(userId);
            string date = DateTime.Now.Date.ToString("yyyy-MM-dd");
            string now = DateTime.Now.ToString();
            string cgdbh = GetNewCgdbh();

            sb.Append("insert into Wsdhdh( DjLsh,dhdh,djyh,Dmbh,Dmmc,djsj,rq,sj,clzt,djzt,DjStZdh,DjFtZdh,DjCount,DjBtZdh,dhzj) values(");
            sb.Append("'" + djlsh + "','" + dhdh + "','用户网站 ','" + userId + "','" + userdmmc + "','" + now + "','" + date + "','" + now + "','未受理 ','未审查 ','" + 0 + "','" + 0 + "','" + 1 + "','" + hh1 + "'," + dgje + ")");
            return sb.ToString();
        }
        public int GetHh(string userId)
        {
            string sql = "select top 1 hh from web_cart where user_sn='" + userId + "' order by hh desc";

            int hh1 = 0;
            object obj = SQLHelper.GetSingle(sql);

            if (obj != null && obj.ToString() != "")
            {
                hh1 = int.Parse(SQLHelper.GetSingle(sql).ToString());
            }

            return hh1;

        }
        private string GetNewCgdbh()
        {

            string sql = "Select DjLsh From STDjLsh where djname='WSDHD'";

            string zhdjh = SQLHelper.GetSingle(sql).ToString();

            if (zhdjh.Length < 8)
            {
                zhdjh = zhdjh.PadLeft(8, '0');
            }
            string dhdh = "WSDH" + zhdjh;
            return dhdh;
        }
        public string GetNewYDBH()
        {
        
            string sql = "Select DjLsh From STDjLsh where djname='YDGHD'";
            object obj = SQLHelper.GetSingle(sql);
            string zhdjh = "";
            if (obj != null && obj.ToString() != "")
            {
                zhdjh = obj.ToString();
            }
            if (zhdjh.Length < 8)
            {
                zhdjh = zhdjh.PadLeft(8, '0');
            }
            string dhdh = "YDGH" + zhdjh;
            return dhdh;
        }
        public int GetNewDjbtzdh()
        {
            string sql = "select top 1 djbtzdh from cgjhdh order by  djbtzdh desc";
            object obj = SQLHelper.GetSingle(sql);
            int newDjbt = 0;
            if (obj != null && obj.ToString() != "")
            {
                newDjbt = int.Parse(obj.ToString());
            }
            newDjbt = newDjbt + 1;
            return newDjbt;
        }

        
        /// <summary>
        /// 获取订购单流水号
        /// </summary>
        /// <returns></returns>
        public int GetDjLsh1()
        {
            string sql = "select max(DjLsh) from Wsdhdh";
            object obj = SQLHelper.GetSingle(sql);
            int djlsh = 0;
            if (obj != null && obj.ToString() != "")
            {
                djlsh = int.Parse(obj.ToString());
            }
            djlsh = djlsh + 1;
            return djlsh;

        }
        /// <summary>
        /// 获取预订单流水号
        /// </summary>
        /// <returns></returns>
        public int GetDjLsh2()
        {
            string sql = "select max(DjLsh) from ygddH";
            object obj = SQLHelper.GetSingle(sql);
            int djlsh = 0;
            if (obj != null && obj.ToString() != "")
            {
                djlsh = int.Parse(obj.ToString());
            }
            djlsh = djlsh + 1;
            return djlsh;

        }
        public string UpdateZhdjh()
        {
            string sql = "UPDATE STDjLsh SET DjLsh =DjLsh+1  WHERE djname='WSDHD'";

            return sql;

        }
        public string UpdateYdd()
        {
            string sql = "UPDATE STDjLsh SET DjLsh =DjLsh+1  WHERE djname='YDGHD'";

            return sql;

        }
        public string DeleteCart(string user_sn)
        {
            string sql = "delete web_cart  where user_sn='" + user_sn + "'";
            return sql;
        }

        public int InsertWsdhd(List<string> sqlList)
        {
            int ss = SQLHelper.ExecuteSqlTran(sqlList);
            return ss;
        }

        #endregion


        public DataTable GetbuyDate()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select  top 30  CONVERT(varchar(10),xgsj,121)  date,count(*)   number,'<a href=\"Product-date.aspx?tm='+CONVERT(varchar(10),xgsj,121)+'\">点击查看</a>'  Operate from  spdaH where DATALENGTH(dbo.spdaH.tp) > 100   ");
            sb.Append(" group by CONVERT(varchar(10),xgsj,121) order by CONVERT(varchar(10),xgsj,121) desc ");
            DataTable dt = SQLHelper.ExecuteDt(sb.ToString());
            return dt;
        }
 
        public List<webCart_Entity> ConvertToEntity(DataTable dt)
        {
            // 定义集合
            List<webCart_Entity> ts = new List<webCart_Entity>();
            // 获得此模型的类型
            Type type = typeof(webCart_Entity);
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                webCart_Entity t = new webCart_Entity();
                // 获得此模型的公共属性
                PropertyInfo[] propertys = t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;
                    // 检查DataTable是否包含此列
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter
                        if (!pi.CanWrite) continue;
                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                ts.Add(t);
            }
            return ts;
        }


    
        private bool IsExistProductInOrder(string tmh, string djlsh)
        {
            bool isExist = false;
            StringBuilder sb = new StringBuilder();
            sb.Append(" select count(1) from web_order_list_st list ");
            sb.Append("inner join web_order_edit_st edit on list.order_sn=edit.order_sn ");
            sb.Append("where list.djlsh=" + djlsh + " and product_sn='" + tmh + "' ");

            object obj = SQLHelper.GetSingle(sb.ToString());
            if (obj != null && obj.ToString() != "")
            {
                if (int.Parse(obj.ToString()) > 0)
                    isExist = true;
            }

            return isExist;
        }
        private bool IsExistYProductInOrder(string tmh, string djlsh)
        {
            bool isExist = false;
            StringBuilder sb = new StringBuilder();
            sb.Append(" select count(1) from ygddB list ");

            sb.Append("where list.djlsh=" + djlsh + " and spbh='" + tmh + "' ");

            object obj = SQLHelper.GetSingle(sb.ToString());
            if (obj != null && obj.ToString() != "")
            {
                if (int.Parse(obj.ToString()) > 0)
                    isExist = true;
            }

            return isExist;
        }


        #endregion

    }
}
